#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
更新数据库模型，添加触发器支持
"""

from app import app, db
from models.models import User, BankCard, Transaction, UserAuth, DepositBusiness
import pymysql
from config import Config
import re

def parse_database_uri():
    """解析数据库连接字符串"""
    uri = Config.SQLALCHEMY_DATABASE_URI
    # 格式: mysql+pymysql://username:password@host/database
    pattern = r'mysql\+pymysql://([^:]+):([^@]+)@([^/]+)/(.+)'
    match = re.match(pattern, uri)
    if match:
        return {
            'user': match.group(1),
            'password': match.group(2),
            'host': match.group(3),
            'database': match.group(4)
        }
    else:
        raise ValueError("无法解析数据库连接字符串")

def create_system_log_table():
    """创建系统日志表"""
    try:
        # 解析数据库配置
        db_config = parse_database_uri()
        
        # 连接数据库
        connection = pymysql.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database'],
            charset='utf8mb4'
        )
        
        with connection.cursor() as cursor:
            # 创建系统日志表
            sql = """
            CREATE TABLE IF NOT EXISTS system_log (
                id INT PRIMARY KEY AUTO_INCREMENT,
                action VARCHAR(50) NOT NULL,
                table_name VARCHAR(50) NOT NULL,
                record_id INT NOT NULL,
                description TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
            """
            cursor.execute(sql)
            
            # 创建索引（MySQL不支持IF NOT EXISTS for INDEX）
            try:
                cursor.execute("CREATE INDEX idx_system_log_action ON system_log(action)")
            except:
                pass  # 索引已存在
            try:
                cursor.execute("CREATE INDEX idx_system_log_created_at ON system_log(created_at)")
            except:
                pass  # 索引已存在
            
            connection.commit()
            print("✅ 系统日志表创建成功")
            
    except Exception as e:
        print(f"❌ 创建系统日志表失败: {e}")
    finally:
        if 'connection' in locals():
            connection.close()

def create_triggers():
    """创建触发器"""
    try:
        # 解析数据库配置
        db_config = parse_database_uri()
        
        # 连接数据库
        connection = pymysql.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database'],
            charset='utf8mb4'
        )
        
        with connection.cursor() as cursor:
            # 删除已存在的触发器
            cursor.execute("DROP TRIGGER IF EXISTS after_user_insert")
            
            # 创建触发器
            trigger_sql = """
            CREATE TRIGGER after_user_insert
            AFTER INSERT ON user
            FOR EACH ROW
            BEGIN
                DECLARE card_number VARCHAR(20);
                DECLARE card_count INT;
                
                -- 生成银行卡号（格式：622202 + 用户ID + 4位随机数）
                SET card_count = (SELECT COUNT(*) FROM bank_card WHERE user_id = NEW.id);
                SET card_number = CONCAT('622202', LPAD(NEW.id, 6, '0'), LPAD(card_count + 1, 4, '0'));
                
                -- 自动创建银行卡记录
                INSERT INTO bank_card (card_number, balance, user_id, deposit_business_id)
                VALUES (card_number, 0.00, NEW.id, 1);
                
                -- 记录日志
                INSERT INTO system_log (action, table_name, record_id, description, created_at)
                VALUES ('AUTO_CREATE_CARD', 'user', NEW.id, CONCAT('自动为用户 ', NEW.name, ' 创建银行卡: ', card_number), NOW());
                
            END
            """
            cursor.execute(trigger_sql)
            
            connection.commit()
            print("✅ 触发器创建成功")
            
            # 验证触发器
            cursor.execute("SHOW TRIGGERS LIKE 'user'")
            triggers = cursor.fetchall()
            print(f"📋 当前用户表触发器: {len(triggers)} 个")
            for trigger in triggers:
                print(f"   - {trigger[0]}: {trigger[1]} {trigger[2]} {trigger[3]}")
            
    except Exception as e:
        print(f"❌ 创建触发器失败: {e}")
    finally:
        if 'connection' in locals():
            connection.close()

def test_trigger():
    """测试触发器功能"""
    try:
        with app.app_context():
            # 创建测试用户
            test_user = User(
                name="测试用户_触发器",
                id_card="123456789012345678",
                phone="13800138000"
            )
            db.session.add(test_user)
            db.session.commit()
            
            print(f"✅ 测试用户创建成功，ID: {test_user.id}")
            
            # 检查是否自动创建了银行卡
            cards = BankCard.query.filter_by(user_id=test_user.id).all()
            print(f"📋 自动创建的银行卡数量: {len(cards)}")
            for card in cards:
                print(f"   - 卡号: {card.card_number}, 余额: {card.balance}")
            
            # 清理测试数据
            db.session.delete(test_user)
            db.session.commit()
            print("🧹 测试数据已清理")
            
    except Exception as e:
        print(f"❌ 测试触发器失败: {e}")

if __name__ == "__main__":
    print("🚀 开始更新数据库模型和触发器...")
    
    # 创建系统日志表
    create_system_log_table()
    
    # 创建触发器
    create_triggers()
    
    # 测试触发器
    print("\n🧪 测试触发器功能...")
    test_trigger()
    
    print("\n✅ 数据库模型和触发器更新完成！") 